﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace BLL.Services.Responses
{
    public class CommentsCollection
    {
        private readonly string _connectionString;

        public CommentsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public Response SelectById(int commentId)
        {
            Response result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.SelectById(conn, commentId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result = GetResponse(reader);
                    }
                }
            }
            return result;
        }

        public IEnumerable<Response> SelectAll()
        {
            var result = new List<Response>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.SelectAll(conn);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var comment = GetResponse(reader);
                        result.Add(comment);
                    }
                }
            }
            return result;
        }

        public Response Create(Response model)
        {
            model.DateOfPublish = DateTime.Now;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.Insert(
                    conn, model.BookId, model.Content, model.DateOfPublish, model.UserId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        model = GetResponse(reader);
                }
            }
            return model;
        }

        public void Save(Response model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.Update(
                    conn, model.ResponseId, model.BookId, model.Content, model.DateOfPublish, model.UserId);

                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int commentId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.Delete(conn, commentId);

                cmd.ExecuteNonQuery();
            }
        }

        public Listing.ResponsesListing GetListingComment(
            int page, int size, string column, int direction, Listing.ResponseFilter filter)
        {
            if (filter == null)
                filter = new Listing.ResponseFilter();

            var result = new Listing.ResponsesListing { Page = page, Size = size, Direction = direction, Column = column, Filter = filter };
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Responses.GetListing(
                    conn, page, size, column, direction, filter.BookId, filter.UserId, filter.DateFrom ?? new DateTime(), filter.DateTo ?? new DateTime());
                cmd.CommandType = CommandType.StoredProcedure;

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new Listing.ResponsesListing.Response();
                        item.ResponseId = reader.GetInt32(0);
                        item.DateOfPublish = reader.GetDateTime(1).ToShortDateString();
                        item.BookId = reader.GetInt32(2);
                        item.BookTitle = reader.GetString(3);
                        item.UserId = reader.GetInt32(4);
                        item.UserEmail = reader.GetString(5);
                        result.Responses.Add(item);
                    }
                }

                result.TotalPages = (int)totalCount.Value % size == 0
                                        ? (int)totalCount.Value / size
                                        : ((int)totalCount.Value / size) + 1;
            }
            return result;
        }

        /// <summary>
        /// Gets the comments by book.
        /// </summary>
        /// <param name="bookId">The book id.</param>
        public IEnumerable<BookResponse> GetCommentsByBook(int bookId)
        {
            var result = new List<BookResponse>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Responses.GetByBook(conn, bookId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new BookResponse();
                        item.Content = reader[0] as string;
                        item.DateOfPublish = reader.GetDateTime(1);
                        item.Login = reader.GetString(2);
                        item.Avatar = reader.GetString(3);
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Get book from data reader
        /// </summary>
        /// <param name="reader">The reader.</param>
        private static Response GetResponse(IDataRecord reader)
        {
            var response = new Response();
            response.ResponseId = reader.GetInt32(0);
            response.BookId = reader.GetInt32(1);
            response.Content = reader[2] as string;
            response.DateOfPublish = reader.GetDateTime(3);
            response.UserId = reader.GetInt32(4);
            return response;
        }
    }
}
